//*********************************************************
//  File:           DataAccess.cs
//  Author:         Gavin McDevitt
//  Created:        January 17, 2009
//  Description:    This class will provide static data 
//      access methods for the application. All access to
//      the database will be done through this class
//*********************************************************

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

namespace Main
{
    public class DataAccess
    {
        //Member variables for database access
        private static DataTable dtResults = new DataTable();
        private static SqlDataAdapter daResults = new SqlDataAdapter();
        private static SqlCommand cmdResults = new SqlCommand();
        //private static string sConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        private static string sConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\RolyPoll.mdf;Integrated Security=True;User Instance=True";

        private static String SetDataDirectory()
        {
            //Connection strings require a full path, we can get this by using the |DataDirectory| keyword

            //If the "DataDirectory" is set to nothing we're running the app in a different domain than usual
            //This is going to be the testing domain
            if (AppDomain.CurrentDomain.GetData("DataDirectory") as string == null)
            {
                //We'll build our path to the data directory using a relative path from the testing directory
                string dataDirectory = System.IO.Path.GetFullPath("..\\..\\..\\Main\\App_Data");
                //And setting the "DataDirectory" keyword to point to that location
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDirectory);
            }
            //Then return the location
            return AppDomain.CurrentDomain.GetData("DataDirectory") as string;
        }

        public static DataTable GetPollByID(int p_Id)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetPollByID";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@PollId", p_Id));
            cmdResults.Connection.Open();

            //Fill the data table with the results of the command
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetOptionsByPollID(int p_Id)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetOptionsByPollID";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@PollId", p_Id));
            cmdResults.Parameters.Add(new SqlParameter("@Error", ""));
            cmdResults.Connection.Open();

            //Fill the data table with the results
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetAllPollsWithOptions()
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetAllPollsWithOptions";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Connection.Open();

            //Fill the data table with the results
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetPollsForUser(Guid p_UserID)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetPollsForUser";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserID));
            cmdResults.Connection.Open();

            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetPollsForFriendsWithLimit(Guid p_UserID, int limit)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetPollsForFriendsWithLimit";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserID));
            cmdResults.Parameters.Add(new SqlParameter("@LimitTo", limit));
            cmdResults.Connection.Open();

            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable CommitPoll(string p_sTitle, string p_sQuestion, string p_sSummary, DateTime p_StartDate, DateTime p_EndDate, Guid p_UserID, bool p_IsPublic)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.CreatePoll";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@Title", p_sTitle));
            cmdResults.Parameters.Add(new SqlParameter("@Question", p_sQuestion));
            cmdResults.Parameters.Add(new SqlParameter("@Summary", p_sSummary));
            cmdResults.Parameters.Add(new SqlParameter("@StartDate", p_StartDate));
            cmdResults.Parameters.Add(new SqlParameter("@EndDate", p_EndDate));
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserID));
            cmdResults.Parameters.Add(new SqlParameter("@IsPublic", p_IsPublic));
            cmdResults.Connection.Open();

            //Execute the insert
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static void CommitPollOption(int p_iPollId, string p_sPollOption)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.CreatePollOption";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@PollId", p_iPollId));
            cmdResults.Parameters.Add(new SqlParameter("@PollOption", p_sPollOption));
            cmdResults.Parameters.Add(new SqlParameter("@Error", ""));
            cmdResults.Connection.Open();

            //Execute the insert
            cmdResults.ExecuteNonQuery();

            cmdResults.Connection.Close();
        }

        public static DataTable GetVote(Guid p_UserID, int p_PollID, int p_OptionSelectedID)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetVote";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserID));
            cmdResults.Parameters.Add(new SqlParameter("@PollID", p_PollID));
            cmdResults.Parameters.Add(new SqlParameter("@OptionSelectedID", p_OptionSelectedID));
            cmdResults.Connection.Open();

            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetVoteSummaryForPoll(int p_PollID)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetVoteSummaryForPoll";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@PollID", p_PollID));
            cmdResults.Connection.Open();
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);
            cmdResults.Connection.Close();
            return dtResults;
        }

        public static void CreateVote(Guid p_UserID, int p_PollID, int p_OptionSelectedID)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.CreateVote";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserID));
            cmdResults.Parameters.Add(new SqlParameter("@PollID", p_PollID));
            cmdResults.Parameters.Add(new SqlParameter("@OptionSelectedID", p_OptionSelectedID));
            cmdResults.Connection.Open();


            cmdResults.ExecuteNonQuery();

            cmdResults.Connection.Close();
        }

        public static void CreateComment(Guid p_UserID, int p_PollID, string p_Comment, string p_Title)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.CreateComment";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserID));
            cmdResults.Parameters.Add(new SqlParameter("@PollID", p_PollID));
            cmdResults.Parameters.Add(new SqlParameter("@Comment", p_Comment));
            cmdResults.Parameters.Add(new SqlParameter("@Title", p_Title));
            cmdResults.Connection.Open();

            cmdResults.ExecuteNonQuery();

            cmdResults.Connection.Close();
        }

        public static DataTable GetCommentsForUser(Guid p_UserId)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetCommentsForUser";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", p_UserId));
            cmdResults.Connection.Open();

            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetCommentByID(int p_CommentID)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetCommentByID";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@CommentID", p_CommentID));
            cmdResults.Connection.Open();

            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetCommentsForPoll(int p_PollID)
        {
            ResetVariables();

            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetCommentsForPoll";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@PollID", p_PollID));
            cmdResults.Connection.Open();

            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        private static void ResetVariables()
        {
            SetDataDirectory();
            dtResults = new DataTable();
            daResults = new SqlDataAdapter();
            cmdResults = new SqlCommand();

        }

        public static DataTable GetAllUsers()
        {
            ResetVariables();
            //Setup the command
            //cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandType = CommandType.Text;

            //cmdResults.CommandText = "dbo.GetAllPollsWithOptions";
            cmdResults.CommandText = "SELECT * FROM aspnet_Users;";

            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Connection.Open();

            //Fill the data table with the results
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetUserByUserName(String p_UserName)
        {
            ResetVariables();
            cmdResults.CommandType = CommandType.Text;
            cmdResults.CommandText = "SELECT * FROM aspnet_Users WHERE (UserName = N'" + p_UserName +"')";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Connection.Open();
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);
            cmdResults.Connection.Close();
            return dtResults;
        }


        #region "Friend functionality methods"
        public static DataTable GetFriendsByUserID(Guid userID)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetFriendsByUserID";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", userID));
            cmdResults.Connection.Open();

            //Fill the data table with the results
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable CreateFriendship(Guid userID, Guid friendID)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.CreateFriendship";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", userID));
            cmdResults.Parameters.Add(new SqlParameter("@FriendID", friendID));
            cmdResults.Connection.Open();
            //Execute the insert
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }

        public static DataTable GetFriendShip(Guid userID, Guid friendID)
        {
            ResetVariables();

            //Setup the command
            cmdResults.CommandType = CommandType.StoredProcedure;
            cmdResults.CommandText = "dbo.GetFriendship";
            cmdResults.Connection = new SqlConnection(sConnection);
            cmdResults.Parameters.Add(new SqlParameter("@UserID", userID));
            cmdResults.Parameters.Add(new SqlParameter("@FriendID", friendID));
            cmdResults.Connection.Open();
            //Find the friendships
            daResults.SelectCommand = cmdResults;
            daResults.Fill(dtResults);

            cmdResults.Connection.Close();

            return dtResults;
        }



        #endregion
    }
}
